# -*- coding: utf-8 -*-
import re
import MySQLdb
import requests
from scrapy.selector import Selector
conn = MySQLdb.connect(host="39.106.29.90",user="root",passwd="root",db="jd",charset="utf8")
cursor = conn.cursor()

from scrapy.cmdline import execute

import sys
import os
import time


def crawl_ips():

    # 爬取西刺的免费ip代理
    headers = {
        "User-Agent": "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.132 Safari/537.36"}
    for i in range(5):
        res = requests.get(
             "http://www.xicidaili.com/wn/{0}".format(i),
             headers=headers)


        selector = Selector(text=res.text)
        all_trs = selector.css("#ip_list tr")

        ip_list = []
        for tr in all_trs[1:]:
            time.sleep(60)
            speed_str = tr.css(".bar::attr(title)").extract()[0]
            if speed_str:
                speed = float(speed_str.split("秒")[0])
            all_texts = tr.css("td::text").extract()
            match_obj1 = re.match(".*'HTTPS'.*", str(all_texts))
            match_obj2 = re.match(".*'HTTP'.*", str(all_texts))
            proxy_type = ""
            if match_obj1:
                proxy_type = "HTTPS"
            elif match_obj2:
                proxy_type = "HTTP"
            ip = all_texts[0]
            port = all_texts[1]

            ip_list.append((ip, port, proxy_type, speed))

        for ip_info in ip_list:
            cursor.execute(
                "insert proxy_ip(ip, port, proxy_type, speed) VALUES('{0}', '{1}', {2}, '{3}')".format(
                    ip_info[0], ip_info[1], ip_info[3], ip_info[2]))

            conn.commit()
        get_ip = GetIP()
        get_ip.get_random_ip()





class GetIP(object):
    def delete_ip(self, ip):
        # 从数据库中删除无效的ip
        delete_sql = """
            delete from proxy_ip where ip='{0}'
        """.format(ip)
        cursor.execute(delete_sql)
        conn.commit()
        return True

    def judge_ip(self, ip, port):
        print(ip)
        print(port)
        # 判断ip是否可用
        http_url = "https://www.baidu.com"
        proxy_url = "https://{0}:{1}".format(ip, port)
        try:
            proxy_dict = {
                "https": proxy_url,
            }
            response = requests.get(http_url, proxies=proxy_dict)
        except Exception as e:
            print("invalid ip and port")
            self.delete_ip(ip)
            return False
        else:
            code = response.status_code
            if code >= 200 and code < 300:
                print("effective ip")
                return True
            else:
                print("invalid ip and port")
                self.delete_ip(ip)
                return False

    def get_random_ip(self):
        # 获取ip表总数,如果剩余ip小于5就重新获取ip
        count_sql = """
             SELECT count(proxy_type) FROM proxy_ip WHERE proxy_type='HTTPS'
             """
        count = cursor.execute(count_sql)
        num = cursor.fetchall()[0][0]
        print(num)
        if num<5:
            crawl_ips()
        # 获取随机ip
        random_sql = """SELECT ip,port FROM proxy_ip WHERE proxy_type='HTTPS'
         ORDER BY RAND()
         LIMIT 1
        """
        result = cursor.execute(random_sql)

        for ip_info in cursor.fetchall():
           ip = ip_info[0]
           port = ip_info[1]
           judge_re = self.judge_ip(ip, port)
           if judge_re:
               return "http://{0}:{1}".format(ip, port)
           else:
               return self.get_random_ip()




# print (crawl_ips())
if __name__ == "__main__":
    get_ip = GetIP()
    get_ip.get_random_ip()